<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
	<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
	<title>JCite With Excel</title>
    <style media="screen" type="text/css">
		
    </style>
	<link href="style_screen.css" rel="stylesheet" type="text/css" media="screen" /><link href="style_print.css" rel="stylesheet" type="text/css" media="print" />
</head>

<body>
    <div class="all">
		<div class="header">
<div class="nav crumbs">
	<a href="http://arrenbrecht.ch/">arrenbrecht.ch</a> &gt; <a href="index.htm">JCite</a> &gt; 
</div>


		</div>
        <div class="content">

<h1>JCite With Excel</h1>

	<p>This document shows how JCite can cite examples from within <a href="http://office.microsoft.com/">Microsoft Excel</a> spreadsheets.</p>


<div class="toc"><ul class="toc toc1"><li><a href="#pagetoc__1">Citing An Entire Sheet</a></li><li><a href="#pagetoc__2">Citing Ranges</a></li><li><a href="#pagetoc__3">Citing Formulas Only</a></li><li><a href="#pagetoc__4">Style Sheet</a></li></ul></div>
	<h5>Note</h5>

	<p>You need to enable the Excel citing plugin for this to work. See the <a href="usage.htm">notes about running JCite</a> for details.</p>


	<h2><a name="pagetoc__1">Citing An Entire Sheet</a></h2>

	<p>Using JCite, you can very easily cite the entire first sheet of an Excel workbook file:</p>

	<table class="xl">
	<thead>
		<tr>
			<td/>
			<td>A</td>
			<td>B</td>
			<td>C</td>
			<td>D</td>
			<td>E</td>
			<td>F</td>
			<td>G</td>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="xl-row">1</td>
			<td><b><span style="text-decoration: underline;">Inputs</span></b></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">2</td>
			<td>Orders for last 3 months</td>
			<td class="xl-r2">Total</td>
			<td class="xl-r2">Date</td>
			<td class="xl-r2">DaysBack</td>
			<td class="xl-r2">Weight</td>
			<td class="xl-r2">Value</td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">3</td>
			<td></td>
			<td class="xl-num xl-r1">450<br/><span class="xl-name">(OrderTotal)</span></td>
			<td class="xl-date xl-r1">8/10/06<br/><span class="xl-name">(OrderDate)</span></td>
			<td class="xl-num xl-r1">97<br/><span class="xl-exp">=B$11-C3</span></td>
			<td class="xl-num xl-r1">-8%<br/><span class="xl-exp">=(90.0-D3)/90.0</span></td>
			<td class="xl-num xl-r1">-35<br/><span class="xl-exp">=B3*E3</span></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">4</td>
			<td></td>
			<td class="xl-num xl-r1">1320</td>
			<td class="xl-date xl-r1">8/29/06</td>
			<td class="xl-num xl-r1">78<br/><span class="xl-exp">=B$11-C4</span></td>
			<td class="xl-num xl-r1">13%<br/><span class="xl-exp">=(90.0-D4)/90.0</span></td>
			<td class="xl-num xl-r1">176<br/><span class="xl-exp">=B4*E4</span></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">5</td>
			<td></td>
			<td class="xl-num xl-r1">1540</td>
			<td class="xl-date xl-r1">9/24/06</td>
			<td class="xl-num xl-r1">52<br/><span class="xl-exp">=B$11-C5</span></td>
			<td class="xl-num xl-r1">42%<br/><span class="xl-exp">=(90.0-D5)/90.0</span></td>
			<td class="xl-num xl-r1">650.222<br/><span class="xl-exp">=B5*E5</span></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">6</td>
			<td></td>
			<td class="xl-num xl-r1">60</td>
			<td class="xl-date xl-r1">10/8/06</td>
			<td class="xl-num xl-r1">38<br/><span class="xl-exp">=B$11-C6</span></td>
			<td class="xl-num xl-r1">58%<br/><span class="xl-exp">=(90.0-D6)/90.0</span></td>
			<td class="xl-num xl-r1">34.667<br/><span class="xl-exp">=B6*E6</span></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">7</td>
			<td></td>
			<td class="xl-num xl-r1">5870</td>
			<td class="xl-date xl-r1">11/5/06</td>
			<td class="xl-num xl-r1">10<br/><span class="xl-exp">=B$11-C7</span></td>
			<td class="xl-num xl-r1">89%<br/><span class="xl-exp">=(90.0-D7)/90.0</span></td>
			<td class="xl-num xl-r1">5217.778<br/><span class="xl-exp">=B7*E7</span></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">8</td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">9</td>
			<td><b><span style="text-decoration: underline;">Intermediates</span></b></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">10</td>
			<td>Total Value</td>
			<td class="xl-num">6043.667<br/><span class="xl-exp">=SUM(F3:F7)</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">11</td>
			<td>CurrentDate; TODAY() really</td>
			<td class="xl-date">11/15/06</td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">12</td>
			<td>&gt; 0 ?</td>
			<td>true<br/><span class="xl-exp">=B10&gt;0.0</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">13</td>
			<td>&lt; 1'000'000 ?</td>
			<td>true<br/><span class="xl-exp">=B10&lt;1000000.0</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">14</td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">15</td>
			<td><b><span style="text-decoration: underline;">Outputs</span></b></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		<tr>
			<td class="xl-row">16</td>
			<td>Rating</td>
			<td class="xl-num">3<br/><span class="xl-exp">=MATCH(B10,C16:G16)</span><br/><span class="xl-name">(Rating)</span></td>
			<td class="xl-num">0</td>
			<td class="xl-num">2000</td>
			<td class="xl-num">5000</td>
			<td class="xl-num">7500</td>
			<td class="xl-num">10000</td>
		</tr>
		<tr>
			<td class="xl-row">17</td>
			<td>Plausible</td>
			<td>true<br/><span class="xl-exp">=AND(B12:B13)</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
	</tbody>
</table>
<br/><span class="xl-r1">B3:F7</span> <span class="xl-name">(OrdersForLastThreeMonths)</span><br/><span class="xl-r2">B2:F2</span> <span class="xl-name">(OrdersHeader)</span>


	<p>This was produced by the following JCite instruction in the source <span class="caps">HTML</span> document:</p>


<pre class="jc">[x<b></b>c:EntireSheet.xls]</pre>
	<p>The part <code>EntireSheet.xls</code> tells JCite what Excel file to cite from. Note how names denoting single cells are shown directly in the cell, while named ranges are colored and their names listed at the bottom.</p>


	<h2><a name="pagetoc__2">Citing Ranges</a></h2>

	<p>You can also cite only a single range instead of an entire sheet:</p>

	<table class="xl">
	<thead>
		<tr>
			<td/>
			<td>B</td>
			<td>C</td>
			<td>D</td>
			<td>E</td>
			<td>F</td>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="xl-row">3</td>
			<td class="xl-num">450<br/><span class="xl-name">(OrderTotal)</span></td>
			<td class="xl-date">8/10/06<br/><span class="xl-name">(OrderDate)</span></td>
			<td class="xl-num">97<br/><span class="xl-exp">=B$11-C3</span></td>
			<td class="xl-num">-8%<br/><span class="xl-exp">=(90.0-D3)/90.0</span></td>
			<td class="xl-num">-35<br/><span class="xl-exp">=B3*E3</span></td>
		</tr>
		<tr>
			<td class="xl-row">4</td>
			<td class="xl-num">1320</td>
			<td class="xl-date">8/29/06</td>
			<td class="xl-num">78<br/><span class="xl-exp">=B$11-C4</span></td>
			<td class="xl-num">13%<br/><span class="xl-exp">=(90.0-D4)/90.0</span></td>
			<td class="xl-num">176<br/><span class="xl-exp">=B4*E4</span></td>
		</tr>
		<tr>
			<td class="xl-row">5</td>
			<td class="xl-num">1540</td>
			<td class="xl-date">9/24/06</td>
			<td class="xl-num">52<br/><span class="xl-exp">=B$11-C5</span></td>
			<td class="xl-num">42%<br/><span class="xl-exp">=(90.0-D5)/90.0</span></td>
			<td class="xl-num">650.222<br/><span class="xl-exp">=B5*E5</span></td>
		</tr>
		<tr>
			<td class="xl-row">6</td>
			<td class="xl-num">60</td>
			<td class="xl-date">10/8/06</td>
			<td class="xl-num">38<br/><span class="xl-exp">=B$11-C6</span></td>
			<td class="xl-num">58%<br/><span class="xl-exp">=(90.0-D6)/90.0</span></td>
			<td class="xl-num">34.667<br/><span class="xl-exp">=B6*E6</span></td>
		</tr>
		<tr>
			<td class="xl-row">7</td>
			<td class="xl-num">5870</td>
			<td class="xl-date">11/5/06</td>
			<td class="xl-num">10<br/><span class="xl-exp">=B$11-C7</span></td>
			<td class="xl-num">89%<br/><span class="xl-exp">=(90.0-D7)/90.0</span></td>
			<td class="xl-num">5217.778<br/><span class="xl-exp">=B7*E7</span></td>
		</tr>
	</tbody>
</table>



	<p>Here&#8217;s the instruction for this:</p>


<pre class="jc">[x<b></b>c:EntireSheet.xls:OrdersForLastThreeMonths]</pre>
	<p>Note that the named range <em>OrdersHeader</em> is not listed because it does not intersect the shown range.</p>


	<p>Instead of just a single one, you can cite multiple ranges:</p>

	<table class="xl">
	<thead>
		<tr>
			<td/>
			<td>B</td>
			<td>C</td>
			<td>D</td>
			<td>E</td>
			<td>F</td>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="xl-row">2</td>
			<td>Total</td>
			<td>Date</td>
			<td>DaysBack</td>
			<td>Weight</td>
			<td>Value</td>
		</tr>
		<tr>
			<td class="xl-row">3</td>
			<td class="xl-num">450<br/><span class="xl-name">(OrderTotal)</span></td>
			<td class="xl-date">8/10/06<br/><span class="xl-name">(OrderDate)</span></td>
			<td class="xl-num">97<br/><span class="xl-exp">=B$11-C3</span></td>
			<td class="xl-num">-8%<br/><span class="xl-exp">=(90.0-D3)/90.0</span></td>
			<td class="xl-num">-35<br/><span class="xl-exp">=B3*E3</span></td>
		</tr>
		<tr>
			<td class="xl-row">4</td>
			<td class="xl-num">1320</td>
			<td class="xl-date">8/29/06</td>
			<td class="xl-num">78<br/><span class="xl-exp">=B$11-C4</span></td>
			<td class="xl-num">13%<br/><span class="xl-exp">=(90.0-D4)/90.0</span></td>
			<td class="xl-num">176<br/><span class="xl-exp">=B4*E4</span></td>
		</tr>
		<tr>
			<td class="xl-row">5</td>
			<td class="xl-num">1540</td>
			<td class="xl-date">9/24/06</td>
			<td class="xl-num">52<br/><span class="xl-exp">=B$11-C5</span></td>
			<td class="xl-num">42%<br/><span class="xl-exp">=(90.0-D5)/90.0</span></td>
			<td class="xl-num">650.222<br/><span class="xl-exp">=B5*E5</span></td>
		</tr>
		<tr>
			<td class="xl-row">6</td>
			<td class="xl-num">60</td>
			<td class="xl-date">10/8/06</td>
			<td class="xl-num">38<br/><span class="xl-exp">=B$11-C6</span></td>
			<td class="xl-num">58%<br/><span class="xl-exp">=(90.0-D6)/90.0</span></td>
			<td class="xl-num">34.667<br/><span class="xl-exp">=B6*E6</span></td>
		</tr>
		<tr>
			<td class="xl-row">7</td>
			<td class="xl-num">5870</td>
			<td class="xl-date">11/5/06</td>
			<td class="xl-num">10<br/><span class="xl-exp">=B$11-C7</span></td>
			<td class="xl-num">89%<br/><span class="xl-exp">=(90.0-D7)/90.0</span></td>
			<td class="xl-num">5217.778<br/><span class="xl-exp">=B7*E7</span></td>
		</tr>
		<tr>
			<td class="xl-row">16</td>
			<td class="xl-num">3<br/><span class="xl-exp">=MATCH(B10,C16:G16)</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
	</tbody>
</table>



	<p>with an instruction such as:</p>


<pre class="jc">[x<b></b>c:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths,Rating]</pre>
	<p>If you want the ranges to be identified still, suffix them with a <code>+</code> sign:</p>

	<table class="xl">
	<thead>
		<tr>
			<td/>
			<td>B</td>
			<td>C</td>
			<td>D</td>
			<td>E</td>
			<td>F</td>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="xl-row">2</td>
			<td>Total</td>
			<td>Date</td>
			<td>DaysBack</td>
			<td>Weight</td>
			<td>Value</td>
		</tr>
		<tr>
			<td class="xl-row">3</td>
			<td class="xl-num xl-r1">450<br/><span class="xl-name">(OrderTotal)</span></td>
			<td class="xl-date xl-r1">8/10/06<br/><span class="xl-name">(OrderDate)</span></td>
			<td class="xl-num xl-r1">97<br/><span class="xl-exp">=B$11-C3</span></td>
			<td class="xl-num xl-r1">-8%<br/><span class="xl-exp">=(90.0-D3)/90.0</span></td>
			<td class="xl-num xl-r1">-35<br/><span class="xl-exp">=B3*E3</span></td>
		</tr>
		<tr>
			<td class="xl-row">4</td>
			<td class="xl-num xl-r1">1320</td>
			<td class="xl-date xl-r1">8/29/06</td>
			<td class="xl-num xl-r1">78<br/><span class="xl-exp">=B$11-C4</span></td>
			<td class="xl-num xl-r1">13%<br/><span class="xl-exp">=(90.0-D4)/90.0</span></td>
			<td class="xl-num xl-r1">176<br/><span class="xl-exp">=B4*E4</span></td>
		</tr>
		<tr>
			<td class="xl-row">5</td>
			<td class="xl-num xl-r1">1540</td>
			<td class="xl-date xl-r1">9/24/06</td>
			<td class="xl-num xl-r1">52<br/><span class="xl-exp">=B$11-C5</span></td>
			<td class="xl-num xl-r1">42%<br/><span class="xl-exp">=(90.0-D5)/90.0</span></td>
			<td class="xl-num xl-r1">650.222<br/><span class="xl-exp">=B5*E5</span></td>
		</tr>
		<tr>
			<td class="xl-row">6</td>
			<td class="xl-num xl-r1">60</td>
			<td class="xl-date xl-r1">10/8/06</td>
			<td class="xl-num xl-r1">38<br/><span class="xl-exp">=B$11-C6</span></td>
			<td class="xl-num xl-r1">58%<br/><span class="xl-exp">=(90.0-D6)/90.0</span></td>
			<td class="xl-num xl-r1">34.667<br/><span class="xl-exp">=B6*E6</span></td>
		</tr>
		<tr>
			<td class="xl-row">7</td>
			<td class="xl-num xl-r1">5870</td>
			<td class="xl-date xl-r1">11/5/06</td>
			<td class="xl-num xl-r1">10<br/><span class="xl-exp">=B$11-C7</span></td>
			<td class="xl-num xl-r1">89%<br/><span class="xl-exp">=(90.0-D7)/90.0</span></td>
			<td class="xl-num xl-r1">5217.778<br/><span class="xl-exp">=B7*E7</span></td>
		</tr>
		<tr>
			<td class="xl-row">16</td>
			<td class="xl-num">3<br/><span class="xl-exp">=MATCH(B10,C16:G16)</span><br/><span class="xl-name">(Rating)</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
	</tbody>
</table>
<br/><span class="xl-r1">B3:F7</span> <span class="xl-name">(OrdersForLastThreeMonths)</span>


	<p>with an instruction such as:</p>


<pre class="jc">[x<b></b>c:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths+,Rating+]</pre>
	<h2><a name="pagetoc__3">Citing Formulas Only</a></h2>

	<p>In the examples so far, JCite cited cell formulas beneath their saved values. You can make JCite omit the values to make the formulas more prominent:</p>

	<table class="xl">
	<thead>
		<tr>
			<td/>
			<td>B</td>
			<td>C</td>
			<td>D</td>
			<td>E</td>
			<td>F</td>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="xl-row">2</td>
			<td>Total</td>
			<td>Date</td>
			<td>DaysBack</td>
			<td>Weight</td>
			<td>Value</td>
		</tr>
		<tr>
			<td class="xl-row">3</td>
			<td class="xl-num xl-r1">450<br/><span class="xl-name">(OrderTotal)</span></td>
			<td class="xl-date xl-r1">8/10/06<br/><span class="xl-name">(OrderDate)</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B$11-C3</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=(90.0-D3)/90.0</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B3*E3</span></td>
		</tr>
		<tr>
			<td class="xl-row">4</td>
			<td class="xl-num xl-r1">1320</td>
			<td class="xl-date xl-r1">8/29/06</td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B$11-C4</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=(90.0-D4)/90.0</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B4*E4</span></td>
		</tr>
		<tr>
			<td class="xl-row">5</td>
			<td class="xl-num xl-r1">1540</td>
			<td class="xl-date xl-r1">9/24/06</td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B$11-C5</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=(90.0-D5)/90.0</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B5*E5</span></td>
		</tr>
		<tr>
			<td class="xl-row">6</td>
			<td class="xl-num xl-r1">60</td>
			<td class="xl-date xl-r1">10/8/06</td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B$11-C6</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=(90.0-D6)/90.0</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B6*E6</span></td>
		</tr>
		<tr>
			<td class="xl-row">7</td>
			<td class="xl-num xl-r1">5870</td>
			<td class="xl-date xl-r1">11/5/06</td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B$11-C7</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=(90.0-D7)/90.0</span></td>
			<td class="xl-num xl-r1"><span class="xl-exp-only">=B7*E7</span></td>
		</tr>
		<tr>
			<td class="xl-row">16</td>
			<td class="xl-num"><span class="xl-exp-only">=MATCH(B10,C16:G16)</span><br/><span class="xl-name">(Rating)</span></td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
	</tbody>
</table>
<br/><span class="xl-r1">B3:F7</span> <span class="xl-name">(OrdersForLastThreeMonths)</span>


	<p>with an instruction such as:</p>


<pre class="jc">[x<b></b>c:EntireSheet.xls:OrdersHeader,OrdersForLastThreeMonths+,Rating+;formulas-only]</pre>
	<p>The option <code>fo</code> is shorthand for <code>formulas-only</code>.</p>


	<h2><a name="pagetoc__4">Style Sheet</a></h2>

	<p>The formatting of the generated output is <a href="styling.htm">controlled by a <span class="caps">CSS</span> style sheet</a>.</p>

		</div>
        <div class="footer">
<p>Copyright &copy; 2006 by Peter Arrenbrecht. All rights reserved.</p>
        </div>
    </div>
</body>
</html>
